---
title: "SQL-002: Hazard group retrieve"
summary: Retrieve hazard groups effective on a given date
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.Policy
reference_type: package
---

# SQL-002 Hazard group retrieve

Retrieves `HAZARD_GROUP` rows in force on a supplied effective date, reconstructed from `Criteria.RetrieveSQL` string concatenation plus the `BuildWhere()` `StringBuilder` fragment. This is the library's only statement routed through the BOF business-object data portal ([BR-POL-001](../business-rules.md#br-pol-001-hazard-group-effective-dating)).

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-002 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (string concatenation + `StringBuilder` WHERE) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsHazardGroupCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:EffectiveDate` | Input | Date (Oracle `DATE`) | N/A | Effective date used twice in the predicate: lower bound against `H.EFFECTIVE_DATE` and upper bound against `NVL(H.EXPIRATION_DATE, :EffectiveDate + 1)`. Bound once via `DataRequest.AddParm("EffectiveDate", MyCriteria.EffectiveDate)`. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [HAZARD_GROUP](../database-tables.md#hazard_group) | SELECT | `HAZARD_GROUP_ID`, `HAZARD_GROUP_SHORT_DESCR`, `HAZARD_GROUP_LONG_DESCR`, `EFFECTIVE_DATE`, `EXPIRATION_DATE`, `CREATE_DATE`, `CREATE_USER`, `UPDATE_DATE`, `UPDATE_USER`, `NCCI_HAZARD_GROUP_CODE` |

## Sample SQL

```sql
SELECT
  H.HAZARD_GROUP_ID,
  H.HAZARD_GROUP_SHORT_DESCR,
  H.HAZARD_GROUP_LONG_DESCR,
  H.EFFECTIVE_DATE,
  H.EXPIRATION_DATE,
  H.CREATE_DATE,
  H.CREATE_USER,
  H.UPDATE_DATE,
  H.UPDATE_USER,
  H.NCCI_HAZARD_GROUP_CODE
FROM HAZARD_GROUP H
WHERE :EffectiveDate >= H.EFFECTIVE_DATE
  AND :EffectiveDate < NVL(H.EXPIRATION_DATE, :EffectiveDate + 1)
```

## Operations and processes
- OP-01 Retrieve hazard groups effective on a date (see [Operations and processes](../operations-processes.md))
- OP-02 Hydrate a hazard group from a reader (see [Operations and processes](../operations-processes.md))

## Business rules
- [BR-POL-001: Hazard group effective-dating](../business-rules.md#br-pol-001-hazard-group-effective-dating)
- [BR-POL-002: Hazard group data is read-only](../business-rules.md#br-pol-002-hazard-group-data-is-read-only)

## Sources
- SAIF.Business.Policy/clsHazardGroupCollection.vb
- SAIF.Business.Policy/clsHazardGroup.vb